use master
go

create database Class
on
(
name=Class,
filename='D:\text\Class.mdf',
size=10MB,
maxsize=50MB,
filegrowth=10%
)
log on
(
name=Class_log,
filename='D:\text\Class_log.ldf',
size=10MB,
maxsize=50MB,
filegrowth=10%
)
go
use Class
go

create table Class
(
ClassID1 int primary key identity(1,1),
ClassName nvarchar(20) unique not null,
)

insert into Class (ClassName) values ('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班'),('软件8班'),('软件9班'),('软件10班')
select * from Class
update Class set ClassName='软件11班' where ClassID1=1
delete from Class where ClassID1=10

go
use Class
go

create table Student
(
Stuld int primary key identity,
Classld int foreign key references ClassID1(Classld),
StuName nvarchar(20) not null,
StuSex nvarchar(1) check(StuSex in('女','男')) default'男',
StuBirthday date,
Stuphone nvarchar(11) unique,
StuAddress nvarchar(200),
CreateDate datetime default(getdate())
)

select * from Student
insert into Student(Classld,StuName,Stuphone) values(1,'小明',1),(2,'小组',2),(3,'小东',3),(4,'小呀',4),(5,'东',5),(6,'没',6),(7,'却',7),(8,'说',8),(9,'你',9),(10,'是',10)
update Student set CreateDate=2021-3-11
delete from Student where Classld=10

go
use Class
go

create table Course
(
CourseId int primary key identity(1,1),
CourseName nvarchar(100) unique not null,
CourseCredit int default(1) check(CourseCredit>0 and CourseCredit<6) not null,
CourseCredit1 nvarchar(10) check(CourseCredit1='公共科' or CourseCredit1='专业课')
)
insert into Course(CourseName) values('高数'),('毛概'),('大学英语'),('实操'),('体育'),('课外活动')
select * from  Course
update Course set CourseCredit=2 where CourseName='实操'

go
use Class
go

create table score	
(
		Scoreld int primary key identity(1,1),
		Stuld int foreign key references Stuld(stunun001),
		Courseld int foreign key references Course(CourseId),
		Score001 decimal(5,2) unique not null
)
insert into Score(Scoreld) values(123.02),(124.04),(425.00),(125.33),(127.11),(128.22),(129.15),(130.55),(131.45),(777.12),(888.89),(999.99),(111.23),(123.09),(99.23),(95.22),(64.55),(23.11),(20.78),(10.10),(78.00)
	select * from Score
	delete from Score where Scoreld=1
	alter table Score001 add constraint CK_Score001_Score001 check(Score001>0 and Score001<1000)
	alter table Score001 add constraint DK_Score001_Score001 default(0)